﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KraveEntities;
using System.Data.SqlClient;
using System.Collections;

namespace KraveDAL
{
    public class ReservationDAL:DataBase
    {
        public bool AddReservation(Reservation res)
        {
            cmd.CommandText = "SELECT * from reservation WHERE status = 'reserved' ";
            SqlDataReader dr = cmd.ExecuteReader();
            bool flag = true;
            Reservation ret = new Reservation();
            while (dr.Read())
            {
                ret.Name = dr.GetString(1);
                ret.Email = dr.GetString(2);
                ret.PNo = dr.GetString(3);
                ret.Mobile = dr.GetString(4);
                ret.Address = dr.GetString(5);
                ret.PostalCode = Convert.ToInt32(dr.GetInt32(6));
                ret.Date = dr.GetString(7);
                ret.StartTime = dr.GetString(8);
                ret.EndTime = dr.GetString(9);
                ret.EventName = dr.GetString(10);
                ret.MaleGuest = Convert.ToInt32(dr.GetInt32(11));
                ret.FemaleGuest = Convert.ToInt32(dr.GetInt32(12));
                ret.GuestOfHonour = dr.GetString(13);
                ret.Locaion = dr.GetString(14);
                ret.Reference = dr.GetString(15);
                ret.Comments = dr.GetString(16);
                ret.Status = dr.GetString(17);
                
                String retsDate = ret.Date + " " + ret.StartTime;
                DateTime rts = Convert.ToDateTime(retsDate);

                String reteDate = ret.Date + " " + ret.EndTime;
                DateTime rte = Convert.ToDateTime(reteDate);
                

                String ressDate = res.Date + " " + res.StartTime;
                DateTime rss = Convert.ToDateTime(ressDate);

                String reseDate = res.Date + " " + res.EndTime;
                DateTime rse = Convert.ToDateTime(reseDate);

                if ((DateTime.Compare(rss, rts) > 0 && DateTime.Compare(rss, rte) < 0) || (DateTime.Compare(rse, rts) > 0 && DateTime.Compare(rse, rte) < 0) || (DateTime.Compare(rss, rts) == 0 || DateTime.Compare(rse, rte) == 0) || (DateTime.Compare(rss, rts) < 0 && DateTime.Compare(rse, rte) > 0)) 
                {
                    flag = false;
                    break;
                }
            }

            dr.Close();
            if (flag == true)
            {
                cmd.CommandText = "insert into reservation values('" + res.Name + "' , '" + res.Email + "','" + res.PNo + "' , '" + res.Mobile + "' , '" + res.Address + "' , '" + res.PostalCode + "' ,  '" + res.Date + "' , '" + res.StartTime + "', '" + res.EndTime + "' , '" + res.EventName + "' , '" + res.MaleGuest + "' , '" + res.FemaleGuest + "' , '" + res.GuestOfHonour + "' , '" + res.Locaion + "' , '" + res.Reference + "' , '" + res.Comments + "', '" + res.Status + "') ";
                int rows_affected = cmd.ExecuteNonQuery();
                if (rows_affected > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            else
            {
                return false;
            }


        }
        public int get_id()
        {
            int user_id = 0;
            cmd.CommandText = "SELECT * from reservation WHERE status = 'reserved' ";

            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                user_id = dr.GetInt32(0);
            }
            dr.Close();
            return user_id;
        }
        public Reservation Retrieve(int id , ref bool flag)
        {
            cmd.CommandText = "SELECT * from reservation WHERE id ='" + id + "'";

            SqlDataReader dr = cmd.ExecuteReader();
            Reservation res = new Reservation();
            while (dr.Read())
            {
                res.Name = dr.GetString(1);
                res.Email = dr.GetString(2);
                res.PNo = dr.GetString(3);
                res.Mobile = dr.GetString(4);
                res.Address = dr.GetString(5);
                res.PostalCode = Convert.ToInt32(dr.GetInt32(6));
                res.Date = dr.GetString(7);
                res.StartTime = dr.GetString(8);
                res.EndTime = dr.GetString(9);
                res.EventName = dr.GetString(10);
                res.MaleGuest = Convert.ToInt32(dr.GetInt32(11));
                res.FemaleGuest = Convert.ToInt32(dr.GetInt32(12));
                res.GuestOfHonour = dr.GetString(13);
                res.Locaion = dr.GetString(14);
                res.Reference = dr.GetString(15);
                res.Comments = dr.GetString(16);
                res.Status = dr.GetString(17);
            }
            if (res.Status.Equals("cancelled"))
            {
                flag = false;
            }
            dr.Close();
            return res;
        }
        public bool UpdateReservation(int id , Reservation res)
        {
            cmd.CommandText = "SELECT * from reservation WHERE status = 'reserved' AND id<>'"+id+"'";
            SqlDataReader dr = cmd.ExecuteReader();
            bool flag = true;
            Reservation ret = new Reservation();
            while (dr.Read())
            {
                ret.Name = dr.GetString(1);
                ret.Email = dr.GetString(2);
                ret.PNo = dr.GetString(3);
                ret.Mobile = dr.GetString(4);
                ret.Address = dr.GetString(5);
                ret.PostalCode = Convert.ToInt32(dr.GetInt32(6));
                ret.Date = dr.GetString(7);
                ret.StartTime = dr.GetString(8);
                ret.EndTime = dr.GetString(9);
                ret.EventName = dr.GetString(10);
                ret.MaleGuest = Convert.ToInt32(dr.GetInt32(11));
                ret.FemaleGuest = Convert.ToInt32(dr.GetInt32(12));
                ret.GuestOfHonour = dr.GetString(13);
                ret.Locaion = dr.GetString(14);
                ret.Reference = dr.GetString(15);
                ret.Comments = dr.GetString(16);
                ret.Status = dr.GetString(17);
                
                String retsDate = ret.Date + " " + ret.StartTime;
                DateTime rts = DateTime.Parse(retsDate);

                String reteDate = ret.Date + " " + ret.EndTime;
                DateTime rte = DateTime.Parse(reteDate);
                

                String ressDate = res.Date + " " + res.StartTime;
                DateTime rss = DateTime.Parse(ressDate);

                String reseDate = res.Date + " " + res.EndTime;
                DateTime rse = DateTime.Parse(reseDate);

                if ((DateTime.Compare(rss, rts) > 0 && DateTime.Compare(rss, rte) < 0) || (DateTime.Compare(rse, rts) > 0 && DateTime.Compare(rse, rte) < 0) || (DateTime.Compare(rss, rts) == 0 || DateTime.Compare(rse, rte) == 0) || (DateTime.Compare(rss, rts) < 0 && DateTime.Compare(rse, rte) > 0)) 
                {
                    flag = false;
                    break;
                }
            }

            dr.Close();
            if (flag == true)
            {
                cmd.CommandText = "Update reservation SET name = '" + res.Name + "', email = '" + res.Email + "', pno = '" + res.PNo + "' , mobile = '" + res.Mobile + "' , address= '" + res.Address + "' , postalcode = '" + res.PostalCode + "' , date = '" + res.Date + "' , stime = '" + res.StartTime + "', etime = '" + res.EndTime + "' , event = '" + res.EventName + "' , mguest = '" + res.MaleGuest + "' , fguest = '" + res.FemaleGuest + "' , gohonour = '" + res.GuestOfHonour + "' , location = '" + res.Locaion + "' , reference = '" + res.Reference + "' , comments = '" + res.Comments + "'  WHERE id ='" + id + "'";
                int rows_affected = cmd.ExecuteNonQuery();
                if (rows_affected > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            else
            {
                return false;
            }
        }

        public ArrayList RetrieveAll()
        {
            ArrayList al = new ArrayList();

            cmd.CommandText = "SELECT * from reservation WHERE name <> 'admin'";

            SqlDataReader dr = cmd.ExecuteReader();

            
            while (dr.Read())
            {
                Reservation res = new Reservation();
                res.Id = dr.GetInt32(0);
                res.Name = dr.GetString(1);
                res.Email = dr.GetString(2);
                res.PNo = dr.GetString(3);
                res.Mobile = dr.GetString(4);
                res.Address = dr.GetString(5);
                res.PostalCode = Convert.ToInt32(dr.GetInt32(6));
                res.Date = dr.GetString(7);
                res.StartTime = dr.GetString(8);
                res.EndTime = dr.GetString(9);
                res.EventName = dr.GetString(10);
                res.MaleGuest = Convert.ToInt32(dr.GetInt32(11));
                res.FemaleGuest = Convert.ToInt32(dr.GetInt32(12));
                res.GuestOfHonour = dr.GetString(13);
                res.Locaion = dr.GetString(14);
                res.Reference = dr.GetString(15);
                res.Comments = dr.GetString(16);
                res.Status = dr.GetString(17);

                al.Add(res);
                
            }
            dr.Close();

            return al;
        }
        public bool CancelEvent(int id)
        {
            cmd.CommandText = "Update reservation SET status ='cancelled' WHERE id ='"+id+"'";
            int rows_affected = cmd.ExecuteNonQuery();
            if (rows_affected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool SavePhoto(int id, String url)
        {

            cmd.CommandText = "INSERT into gallery VALUES('" +id+ "' , '" + url + "')";
            int rows_affected = cmd.ExecuteNonQuery();
            if (rows_affected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public ArrayList RetrieveAllPhoto()
        {
            ArrayList al = new ArrayList();
            cmd.CommandText = "SELECT * from gallery";

            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                String url = dr.GetString(1);
                al.Add(url);
            }
            return al;
        }
    }
}
